﻿CREATE procedure spAllocateFromLocation( @OrderID int, @CreditAccountID int, @DebitAccountID int,
	@LocationID int, @Qty int, @ProductID int )
as
if not exists ( select 1 from tblInventory where LocationID = @LocationID and ProductID = @ProductID )
	insert into tblInventory ( LocationID, Qty, ProductID, CreateDate )
	select @LocationID, 0, @ProductID, getdate()

while @Qty > 0
begin
	declare @InventoryID int

	set @InventoryID = null
	select top 1 @InventoryID = InventoryID
	from tblInventory
	where LocationID = @LocationID
		and ProductID = @ProductID
		and Qty > 0
	order by CreateDate

	if @InventoryID is null
	begin
		select top 1 @InventoryID = InventoryID
		from tblInventory
		where LocationID = @LocationID
			and ProductID = @ProductID
		order by CreateDate desc

		insert into tblInventoryTransaction ( OrderID, InventoryID, Qty, InventoryTransactionTypeCode, InventoryTransactionStatusCode, CreditAccountID, DebitAccountID )
		values( @OrderID, @InventoryID, 0 - @qty, 'S', 
			'B', @CreditAccountID, @DebitAccountID )

		set @Qty = 0
	end
	else
	begin
		declare @Qty1 int
		select @Qty1 = case when Qty < @Qty then Qty else @Qty end
		from tblInventory
		where InventoryID = @InventoryID

		insert into tblInventoryTransaction ( OrderID, InventoryID, Qty, InventoryTransactionTypeCode, InventoryTransactionStatusCode, CreditAccountID, DebitAccountID )
		values( @OrderID, @InventoryID, 0 - @Qty1, 'S', 
			'A', @CreditAccountID, @DebitAccountID )

		set @Qty = @Qty - @Qty1
	end
end

